/*
 Oracle 21c Compatible Script
 Schema: CT
*/

-- 确保序列存在
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM all_sequences WHERE sequence_owner = 'CT' AND sequence_name = 'SEQ_T_BORROW';
  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE CT.seq_t_borrow START WITH 102 INCREMENT BY 1 NOCACHE NOCYCLE';
  END IF;
END;
/

-- ----------------------------
-- Table structure for T_BORROW
-- ----------------------------
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE CT.T_BORROW';
  EXCEPTION WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE CT.T_BORROW (
  "BORROWID" NUMBER NOT NULL,
  "USERID" NUMBER NOT NULL,
  "BOOKID" NUMBER NOT NULL,
  "BORROWTIME" DATE NOT NULL,
  "RETURNTIME" DATE
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
;

-- ----------------------------
-- Records of T_BORROW
-- ----------------------------
INSERT INTO CT.T_BORROW VALUES ('26', '11', '2', TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('27', '11', '1', TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('28', '11', '4', TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('30', '14', '2', TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('32', '14', '4', TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('38', '14', '1', TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('39', '14', '1', TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('40', '14', '1', TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO CT.T_BORROW VALUES ('101', '14', '2', TO_DATE('2021-12-04 15:00:00', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2021-12-04 15:00:06', 'SYYYY-MM-DD HH24:MI:SS'));

-- ----------------------------
-- Primary Key structure for table T_BORROW
-- ----------------------------
ALTER TABLE CT.T_BORROW ADD CONSTRAINT "SYS_C0011127" PRIMARY KEY ("BORROWID");

-- ----------------------------
-- Checks structure for table T_BORROW
-- ----------------------------
ALTER TABLE CT.T_BORROW ADD CONSTRAINT "SYS_C0011122" CHECK ("BORROWID" IS NOT NULL) ENABLE;
ALTER TABLE CT.T_BORROW ADD CONSTRAINT "SYS_C0011123" CHECK ("USERID" IS NOT NULL) ENABLE;
ALTER TABLE CT.T_BORROW ADD CONSTRAINT "SYS_C0011124" CHECK ("BOOKID" IS NOT NULL) ENABLE;
ALTER TABLE CT.T_BORROW ADD CONSTRAINT "SYS_C0011125" CHECK ("BORROWTIME" IS NOT NULL) ENABLE;

-- ----------------------------
-- Triggers structure for table T_BORROW
-- ----------------------------
CREATE OR REPLACE TRIGGER CT.t_borrow_trig 
BEFORE INSERT ON CT.T_BORROW 
FOR EACH ROW
BEGIN
    SELECT seq_t_borrow.nextval INTO :new.borrowid FROM dual;
END;
/



CREATE TABLE "SYSTEM"."T_BORROW" (
                                     "BORROWID" NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 102) PRIMARY KEY, -- 初始值从102开始
                                     "USERID" NUMBER NOT NULL,
                                     "BOOKID" NUMBER NOT NULL,
                                     "BORROWTIME" DATE NOT NULL,
                                     "RETURNTIME" DATE
)
    NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (BUFFER_POOL DEFAULT)
DISABLE ROW MOVEMENT;

-- 插入数据（移除显式BORROWID）
INSERT INTO "SYSTEM"."T_BORROW" ("USERID", "BOOKID", "BORROWTIME", "RETURNTIME")
VALUES (11, 2, TIMESTAMP '2021-11-18 00:00:00', TIMESTAMP '2021-11-18 00:00:00');
